#!/usr/bin/python
#coding=utf-8
from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
import os
import sys
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL,FLOAT
from common import df_operator as op, db_utils as util


PRE_Q602_SUBPC2009 = """
    CREATE TABLE BGTARAS1.PRE_Q602_SUBPC2009(
        PROD_DATE VARCHAR(8),
        TEAM VARCHAR(5),
        DATE_TIME VARCHAR(14),
        WATER DECIMAL(24,6),
        OIL DECIMAL(24,6),
        START_TIME VARCHAR(14),
        END_TIME VARCHAR(14)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

def createSUBPC2009(start_time,end_time):
    #加油加水临时表
    util.createTmpTable(conn_rds,"BGTARAS1","PRE_Q602_SUBPC2009",PRE_Q602_SUBPC2009)

    sql = """
        SELECT
            B.prod_date,
            CASE B.CLASS_CODE
                WHEN '1' THEN '甲' WHEN '2' THEN '乙'
                WHEN '3' THEN '丙' ELSE '丁' END AS TEAM,
            DATE_TIME,SUM(A.TOTAL_WATER) AS WATER,SUM(A.TOTAL_OIL) AS OIL,B.start_time,B.end_time
        FROM
            BGTAROQ602.T_ODS_SUBPC2009 A,BGTARSSI00.T_DWD_WH_SBOPSI_BASE_SU_J003 B
        WHERE
            CONCAT(a.date_time,'00')=b.end_time AND b.start_time>='"""+start_time+"""'
        AND b.end_time<='"""+end_time+"""'
        GROUP BY
            B.prod_date,B.CLASS_CODE,A.DATE_TIME,B.start_time,B.end_time
    """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PRE_Q602_SUBPC2009", "BGTARAS1")

def createQ602Roll(start_time,end_time):
    PRE_L2_SUBPC20032 = """
        CREATE TABLE BGTARAS1.PRE_L2_SUBPC20032(
            UNIT_CODE VARCHAR(4),
            PROD_COILNO VARCHAR(11),
            START_TIME VARCHAR(14),
            END_TIME VARCHAR(14),
            ROLL_TYPE VARCHAR(5),
            ROLL_NO VARCHAR(10)
        )NOT LOGGED INITIALLY
        IN "BGTARAS1"
    """

    try:
        #查卷信息
        sql_mat = """
            SELECT A.ACCT,A.COST_CENTER,A.UNIT_CODE,A.CLASS,A.SHIFT,A.PRODUCE_TIME,A.PROD_COILNO,A.ENTRY_COILNO,A.STEELNO,
            A.WIDTH_CODE,A.THICK_CODE,A.ENTRY_THICK_CODE 
            FROM BGTARAS1.T_ADS_FACT_SICB_CD0201 A 
            WHERE A.UNIT_CODE='Q602' AND A.PRODUCE_TIME BETWEEN '"""+start_time+"""' AND '"""+end_time+"""'
        """
        mat_df = util.query(conn_rds, sql=sql_mat)
        matString = ','.join(map(lambda x: "'" + x + "'", mat_df["prod_coilno"]))#拼接卷号，拼成用逗号分隔的字符串

        if len(matString)>0:
            #在RDS里查辊号信息
            sql_roll = """
                SELECT START_TIME,END_TIME,PRODUCT_COIL_NO as PRODUCT_COIL_NO_2,
                                FIRST_IMR_RIGHT_TOP_ROLL_ID,
                                FIRST_IMR_RIGHT_BOT_ROLL_ID,
                                FIRST_IMR_LEFT_TOP_ROLL_ID,
                                FIRST_IMR_LEFT_BOT_ROLL_ID,
                                SECOND_IMR_RIGHT_TOP_ROLL_ID,
                                SECOND_IMR_RIGHT_BOT_ROLL_ID,
                                SECOND_IMR_CENTER_TOP_ROLL_ID,
                                SECOND_IMR_CENTER_BOT_ROLL_ID,
                                SECOND_IMR_LEFT_TOP_ROLL_ID,
                                SECOND_IMR_LEFT_BOT_ROLL_ID,
                                TOP_WORK_ROLL_ID_1,BOT_WORK_ROLL_ID_1,TOP_WORK_ROLL_ID_LP,BOT_WORK_ROLL_ID_LP
                FROM BGTAROQ602.T_ODS_SUBPC20032  WHERE PRODUCT_COIL_NO IN ("""+matString+""")
            """
            df = util.query(conn_rds, sql=sql_roll)
            df = pd.melt(df, id_vars=['start_time', 'end_time', 'product_coil_no_2'], value_vars=['first_imr_right_top_roll_id',
                                                                             'first_imr_right_bot_roll_id',
                                                                             'first_imr_left_top_roll_id',
                                                                             'first_imr_left_bot_roll_id',
                                                                             'second_imr_right_top_roll_id',
                                                                             'second_imr_right_bot_roll_id',
                                                                             'second_imr_center_top_roll_id',
                                                                             'second_imr_center_bot_roll_id',
                                                                             'second_imr_left_top_roll_id',
                                                                             'second_imr_left_bot_roll_id',
                                                                             'top_work_roll_id_1',
                                                                             'bot_work_roll_id_1', 'top_work_roll_id_lp',
                                                                             'bot_work_roll_id_lp'],
                         var_name="type",
                         value_name="roll_no")
            df['type'][(df['type'] == 'first_imr_right_top_roll_id')] = 'X1'
            df['type'][(df['type'] == 'first_imr_right_bot_roll_id')] = 'X2'
            df['type'][(df['type'] == 'first_imr_left_top_roll_id')] = 'X3'
            df['type'][(df['type'] == 'first_imr_left_bot_roll_id')] = 'X4'
            df['type'][(df['type'] == 'second_imr_right_top_roll_id')] = 'YZ1'
            df['type'][(df['type'] == 'second_imr_right_bot_roll_id')] = 'YZ2'
            df['type'][(df['type'] == 'second_imr_center_top_roll_id')] = 'YZ3'
            df['type'][(df['type'] == 'second_imr_center_bot_roll_id')] = 'YZ4'
            df['type'][(df['type'] == 'second_imr_left_top_roll_id')] = 'YZ5'
            df['type'][(df['type'] == 'second_imr_left_bot_roll_id')] = 'YZ6'
            df['type'][(df['type'] == 'top_work_roll_id_1')] = 'W1'
            df['type'][(df['type'] == 'bot_work_roll_id_1')] = 'W2'
            df['type'][(df['type'] == 'top_work_roll_id_lp')] = 'W3'
            df['type'][(df['type'] == 'bot_work_roll_id_lp')] = 'W4'

            # 与卷信息拼接，并生成临时表数据
            df_tmp_table = op.merge_df(mat_df, df,[["prod_coilno"], ["product_coil_no_2"]],how="left")
            df_tmp_table = df_tmp_table[["unit_code", "prod_coilno", "start_time", "end_time", "type", "roll_no"]]
            df_tmp_table.rename(columns={'type': 'roll_type'}, inplace=True)

            # 新建临时表，插入数据
            if df_tmp_table.__len__() > 0:
                # 建表
                util.createTmpTable(conn_rds, "BGTARAS1", "PRE_L2_SUBPC20032", PRE_L2_SUBPC20032)
                dtypedict = op.get_dict_type(df_tmp_table)
                util.insert(df_tmp_table, conn_rds, "PRE_L2_SUBPC20032", "BGTARAS1", dtypedict)

            #删除旧的卷数据
            sql_del_1 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'X%' AND UNIT_CODE='Q602')
                AND TYPE LIKE 'X%' AND UNIT_CODE='Q602' """
            conn_rds.execute(sql_del_1)

            # 删除旧的卷数据
            sql_del_2 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'YZ%' AND UNIT_CODE='Q602')
                AND TYPE LIKE 'YZ%' AND UNIT_CODE='Q602' """
            conn_rds.execute(sql_del_2)

            # 删除旧的卷数据
            sql_del_3 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'W%' AND UNIT_CODE='Q602')
                AND TYPE LIKE 'W%' AND UNIT_CODE='Q602' """
            conn_rds.execute(sql_del_3)

            rollString = ','.join(map(lambda x: "'" + x + "'", df["roll_no"]))
            if len(rollString)>0:
                sql_qty = """select * from TMSIJ4.TTMSIJ4RL WHERE roller_no in ("""+rollString+""")"""
                df_qty = util.query(conn_db2, sql=sql_qty)
                df2 = op.merge_df(df, df_qty, [["roll_no"], ["roller_no"]], how="inner")
                df2 = df2[df2["grind_end_time"] <= df2["start_time"]]

                df2 = df2[['start_time', 'end_time', 'product_coil_no_2', 'type', 'roll_no','roller_no', 'grind_start_time', 'grind_end_time', 'grind_qty']]
                df2.rename(columns={'type': 'roll_type'}, inplace=True)
                df2.rename(columns={'product_coil_no_2': 'product_coil_no'}, inplace=True)

                PRE_L2_TTMSIJ4RL = """
                    CREATE TABLE BGTARAS1.PRE_L2_TTMSIJ4RL(
                        START_TIME VARCHAR(14),
                        END_TIME VARCHAR(14),
                        PRODUCT_COIL_NO VARCHAR(11),
                        ROLL_TYPE VARCHAR(5),
                        ROLL_NO VARCHAR(10),
                        ROLLER_NO VARCHAR(10),
                        GRIND_START_TIME VARCHAR(14),
                        GRIND_END_TIME VARCHAR(14),
                        GRIND_QTY DECIMAL(24,6)
                    )NOT LOGGED INITIALLY
                    IN "BGTARAS1"
                """
                # 建表
                util.createTmpTable(conn_rds, "BGTARAS1", "PRE_L2_TTMSIJ4RL", PRE_L2_TTMSIJ4RL)
                dtypedict2 = op.get_dict_type(df2)
                util.insert(df2, conn_rds, "PRE_L2_TTMSIJ4RL", "BGTARAS1", dtypedict2)

                #把每个卷分摊的磨削量加入表T_ADS_FACT_SICB_ROLLWT中
                sql_qty = """
                    SELECT
                        ROLLER_NO,
                        GRIND_END_TIME AS ROLLING_END_TIME,
                        END_TIME,
                        ROLL_TYPE AS TYPE,
                        GRIND_QTY AS ROLLING_QTY,
                        PROD_COILNO,
                        UNIT_CODE
                    FROM
                        (SELECT
                                A.ROLLER_NO,
                                b.ROLL_TYPE,
                                A.GRIND_END_TIME,
                                B.START_TIME,
                                A.GRIND_QTY,
                                B.END_TIME,
                                B.PROD_COILNO,
                                B.UNIT_CODE,
                                row_number() over(partition BY b.PROD_COILNO,a.ROLLER_NO ORDER BY ABS(to_date
                                (A.GRIND_END_TIME, 'YYYYMMDDHH24MISS')-to_date(B.START_TIME,'YYYYMMDDHH24MISS')))
                                AS NUM
                            FROM
                                BGTARAS1.PRE_L2_TTMSIJ4RL A,
                                BGTARAS1.PRE_L2_SUBPC20032 b
                            WHERE
                                a.roller_no=b.roll_no
                            AND A.GRIND_END_TIME<=B.START_TIME
                            AND B.UNIT_CODE='Q602')
                    WHERE num=1
                """
                df_result = util.query(conn_rds, sql=sql_qty)
                dtypedict = op.get_dict_type(df_result)
                util.insert(df_result, conn_rds, "T_ADS_FACT_SICB_ROLLWT", "BGTARAS1", dtypedict)

                #删除影响的卷号信息 先删除后新增
                sql_mo_1 = """
                    delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                    select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                    and a.type like 'X%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q602')
                    and UNIT_CODE='Q602' and IND ='64400_X_COST'
                """
                conn_rds.execute(sql_mo_1)

                sql_mo_2 = """
                                delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                and a.type like 'YZ%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q602')
                                and UNIT_CODE='Q602' and IND ='64400_YZ_COST'
                            """
                conn_rds.execute(sql_mo_2)

                sql_mo_3 = """
                               delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                            select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                            and a.type like 'W%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q602')
                                      and UNIT_CODE='Q602' and IND ='64300_COST'
                             """
                conn_rds.execute(sql_mo_3)

                sql_mo_4 = """
                               delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                       select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                       AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q602')
                               and UNIT_CODE='Q602' and IND ='FL'
                            """
                conn_rds.execute(sql_mo_4)
    except Exception as e:
        print(e)

def createQ402Roll(start_time,end_time):
    PRE_L2_SUBPC20032 = """
        CREATE TABLE BGTARAS1.PRE_L2_SUBPC20032(
            UNIT_CODE VARCHAR(4),
            PROD_COILNO VARCHAR(11),
            START_TIME VARCHAR(14),
            END_TIME VARCHAR(14),
            ROLL_TYPE VARCHAR(5),
            ROLL_NO VARCHAR(10)
        )NOT LOGGED INITIALLY
        IN "BGTARAS1"
    """

    try:
        #查卷信息
        sql_mat = """
            with tmp as(
                        SELECT A.UNIT_CODE,A.CLASS,A.shift,A.PRODUCE_TIME,A.PROD_COILNO,A.ENTRY_COILNO,B.START_TIME,B.END_TIME,
                        IMR_RIGHT_TOP_ROLL_ID_1ST,
                        IMR_RIGHT_BOT_ROLL_ID_1ST,
                        IMR_LEFT_TOP_ROLL_ID_1ST,
                        IMR_LEFT_BOT_ROLL_ID_1ST,
                        IMR_RIGHT_TOP_ROLL_ID_2ND,
                        IMR_RIGHT_BOT_ROLL_ID_2ND,
                        IMR_CENTER_TOP_ROLL_ID_2ND,
                        IMR_CENTER_BOT_ROLL_ID_2ND,
                        IMR_LEFT_TOP_ROLL_ID_2ND,
                        IMR_LEFT_BOT_ROLL_ID_2ND,
                        UPPER_WORK_ROLL_NO,LOWER_WORK_ROLL_NO
                        FROM BGTARAS1.T_ADS_FACT_SICB_CD0201 A LEFT JOIN BGTAROQ402.SUBPC2003 B ON A.PROD_COILNO=B.PRODUCT_COIL_NO
                        WHERE A.UNIT_CODE='Q402' AND A.PRODUCE_TIME BETWEEN '"""+start_time+"""' AND '"""+end_time+"""')
                        select A.UNIT_CODE,A.PROD_COILNO,A.START_TIME,A.END_TIME,B.TYPE as roll_type,B.ROLL_NO FROM TMP A,
                                 TABLE(values(A.IMR_RIGHT_TOP_ROLL_ID_1ST,'X1'),(A.IMR_RIGHT_BOT_ROLL_ID_1ST,'X2'),
                                (A.IMR_LEFT_TOP_ROLL_ID_1ST,'X3'),(A.IMR_LEFT_BOT_ROLL_ID_1ST,'X4'),(A.IMR_RIGHT_TOP_ROLL_ID_2ND,'YZ1'),
                                (A.IMR_RIGHT_BOT_ROLL_ID_2ND,'YZ2'),(A.IMR_CENTER_TOP_ROLL_ID_2ND,'YZ3'),(A.IMR_CENTER_BOT_ROLL_ID_2ND,'YZ4'),
                                (A.IMR_LEFT_TOP_ROLL_ID_2ND,'YZ5'),(A.IMR_LEFT_BOT_ROLL_ID_2ND,'YZ6'),
                                 (A.UPPER_WORK_ROLL_NO,'W1'),(A.LOWER_WORK_ROLL_NO,'W2')
                                ) as b(ROLL_NO,type)
        """
        roll_df = util.query(conn_rds, sql=sql_mat)

        # 新建临时表，插入数据
        if roll_df.__len__()>0:
            # 建表
            util.createTmpTable(conn_rds, "BGTARAS1", "PRE_L2_SUBPC20032", PRE_L2_SUBPC20032)
            dtypedict = op.get_dict_type(roll_df)
            util.insert(roll_df, conn_rds, "PRE_L2_SUBPC20032", "BGTARAS1", dtypedict)

            # 删除旧的卷数据
            sql_del_1 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'X%' AND UNIT_CODE='Q402')
                            AND TYPE LIKE 'X%' AND UNIT_CODE='Q402' """
            conn_rds.execute(sql_del_1)

            # 删除旧的卷数据
            sql_del_2 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'YZ%' AND UNIT_CODE='Q402')
                            AND TYPE LIKE 'YZ%' AND UNIT_CODE='Q402' """
            conn_rds.execute(sql_del_2)

            # 删除旧的卷数据
            sql_del_3 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'W%' AND UNIT_CODE='Q402')
                            AND TYPE LIKE 'W%' AND UNIT_CODE='Q402' """
            conn_rds.execute(sql_del_3)

            rollString = ','.join(map(lambda x: "'" + x + "'", roll_df["roll_no"]))
            if len(rollString) > 0:
                sql_qty = """select * from TMSIJ4.TTMSIJ4RL WHERE roller_no in (""" + rollString + """)"""
                df_qty = util.query(conn_db2, sql=sql_qty)
                df2 = op.merge_df(roll_df, df_qty, [["roll_no"], ["roller_no"]], how="inner")
                df2 = df2[df2["grind_end_time"] <= df2["start_time"]]

                df2 = df2[
                    ['start_time', 'end_time', 'prod_coilno', 'roll_type', 'roll_no', 'roller_no', 'grind_start_time',
                     'grind_end_time', 'grind_qty']]
                df2.rename(columns={'prod_coilno': 'product_coil_no'}, inplace=True)

                PRE_L2_TTMSIJ4RL = """
                                CREATE TABLE BGTARAS1.PRE_L2_TTMSIJ4RL(
                                    START_TIME VARCHAR(14),
                                    END_TIME VARCHAR(14),
                                    PRODUCT_COIL_NO VARCHAR(11),
                                    ROLL_TYPE VARCHAR(5),
                                    ROLL_NO VARCHAR(10),
                                    ROLLER_NO VARCHAR(10),
                                    GRIND_START_TIME VARCHAR(14),
                                    GRIND_END_TIME VARCHAR(14),
                                    GRIND_QTY DECIMAL(24,6)
                                )NOT LOGGED INITIALLY
                                IN "BGTARAS1"
                            """
                # 建表
                util.createTmpTable(conn_rds, "BGTARAS1", "PRE_L2_TTMSIJ4RL", PRE_L2_TTMSIJ4RL)
                dtypedict2 = op.get_dict_type(df2)
                util.insert(df2, conn_rds, "PRE_L2_TTMSIJ4RL", "BGTARAS1", dtypedict2)

                # 把每个卷分摊的磨削量加入表T_ADS_FACT_SICB_ROLLWT中
                sql_qty = """
                                SELECT
                                    ROLLER_NO,
                                    GRIND_END_TIME AS ROLLING_END_TIME,
                                    END_TIME,
                                    ROLL_TYPE AS TYPE,
                                    GRIND_QTY AS ROLLING_QTY,
                                    PROD_COILNO,
                                    UNIT_CODE
                                FROM
                                    (SELECT
                                            A.ROLLER_NO,
                                            b.ROLL_TYPE,
                                            A.GRIND_END_TIME,
                                            B.START_TIME,
                                            A.GRIND_QTY,
                                            B.END_TIME,
                                            B.PROD_COILNO,
                                            B.UNIT_CODE,
                                            row_number() over(partition BY b.PROD_COILNO,a.ROLLER_NO ORDER BY ABS(to_date
                                            (A.GRIND_END_TIME, 'YYYYMMDDHH24MISS')-to_date(B.START_TIME,'YYYYMMDDHH24MISS')))
                                            AS NUM
                                        FROM
                                            BGTARAS1.PRE_L2_TTMSIJ4RL A,
                                            BGTARAS1.PRE_L2_SUBPC20032 b
                                        WHERE
                                            a.roller_no=b.roll_no
                                        AND A.GRIND_END_TIME<=B.START_TIME
                                        AND B.UNIT_CODE='Q402')
                                WHERE num=1
                            """
                df_result = util.query(conn_rds, sql=sql_qty)
                dtypedict = op.get_dict_type(df_result)
                util.insert(df_result, conn_rds, "T_ADS_FACT_SICB_ROLLWT", "BGTARAS1", dtypedict)

                # 删除影响的卷号信息 先删除后新增
                sql_mo_1 = """
                                delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                and a.type like 'X%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                and UNIT_CODE='Q402' and IND ='64400_X_COST'
                            """
                conn_rds.execute(sql_mo_1)

                sql_mo_2 = """
                                            delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                            select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                            and a.type like 'YZ%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                            and UNIT_CODE='Q402' and IND ='64400_YZ_COST'
                                        """
                conn_rds.execute(sql_mo_2)

                sql_mo_3 = """
                                           delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                                        select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                                        and a.type like 'W%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                                  and UNIT_CODE='Q402' and IND ='64300_COST'
                                         """
                conn_rds.execute(sql_mo_3)

                sql_mo_4 = """
                                           delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                                   select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                                   AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                           and UNIT_CODE='Q402' and IND ='FL'
                                        """
                conn_rds.execute(sql_mo_4)
    except Exception as e:
        print(e)

def createQ502Roll(start_time,end_time):
    PRE_L2_SUBPC20032 = """
        CREATE TABLE BGTARAS1.PRE_L2_SUBPC20032(
            UNIT_CODE VARCHAR(4),
            PROD_COILNO VARCHAR(11),
            START_TIME VARCHAR(14),
            END_TIME VARCHAR(14),
            ROLL_TYPE VARCHAR(5),
            ROLL_NO VARCHAR(10)
        )NOT LOGGED INITIALLY
        IN "BGTARAS1"
    """

    try:
        #查卷信息
        sql_mat = """
            with tmp as(
                        SELECT A.UNIT_CODE,A.CLASS,A.shift,A.PRODUCE_TIME,A.PROD_COILNO,A.ENTRY_COILNO,
                        CASE WHEN C.PRODSTART IS NULL THEN A.PRODUCE_TIME ELSE C.PRODSTART END AS START_TIME,
                        CASE WHEN C.PRODEND IS NULL THEN A.PRODUCE_TIME ELSE C.PRODEND END AS END_TIME,
                        ROLL_NO03,
                        ROLL_NO05,
                        ROLL_NO04,
                        ROLL_NO06,
                        ROLL_NO07,
                        ROLL_NO10,
                        ROLL_NO08,
                        ROLL_NO11,
                        ROLL_NO09,
                        ROLL_NO12,
                        ROLL_NO01,ROLL_NO02
                        FROM BGTARAS1.T_ADS_FACT_SICB_CD0201 A INNER JOIN BGTAROQ502.FV_BPC_S3_ROLLINFO B ON A.PROD_COILNO=B.TCOILNO
                       INNER JOIN bgtaroq502.T_BPC_PDO C ON A.PROD_COILNO = B.ENCOILID
                        WHERE A.UNIT_CODE='Q502' AND A.PRODUCE_TIME BETWEEN '20211124200000' AND '20211125200000')
                        select A.UNIT_CODE,A.PROD_COILNO,A.START_TIME,A.END_TIME,B.TYPE as roll_type,B.ROLL_NO FROM TMP A,
                                 TABLE(values(A.ROLL_NO03,'X1'),(A.ROLL_NO05,'X2'),
                                (A.ROLL_NO04,'X3'),(A.ROLL_NO06,'X4'),(A.ROLL_NO07,'YZ1'),
                                (A.ROLL_NO10,'YZ2'),(A.ROLL_NO08,'YZ3'),(A.ROLL_NO11,'YZ4'),
                                (A.ROLL_NO09,'YZ5'),(A.ROLL_NO12,'YZ6'),
                                 (A.ROLL_NO01,'W1'),(A.ROLL_NO02,'W2')
                                ) as b(ROLL_NO,type)
        """
        roll_df = util.query(conn_rds, sql=sql_mat)

        # 新建临时表，插入数据
        if roll_df.__len__()>0:
            # 建表
            util.createTmpTable(conn_rds, "BGTARAS1", "PRE_L2_SUBPC20032", PRE_L2_SUBPC20032)
            dtypedict = op.get_dict_type(roll_df)
            util.insert(roll_df, conn_rds, "PRE_L2_SUBPC20032", "BGTARAS1", dtypedict)

            # 删除旧的卷数据
            sql_del_1 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'X%' AND UNIT_CODE='Q402')
                            AND TYPE LIKE 'X%' AND UNIT_CODE='Q402' """
            conn_rds.execute(sql_del_1)

            # 删除旧的卷数据
            sql_del_2 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'YZ%' AND UNIT_CODE='Q402')
                            AND TYPE LIKE 'YZ%' AND UNIT_CODE='Q402' """
            conn_rds.execute(sql_del_2)

            # 删除旧的卷数据
            sql_del_3 = """delete from BGTARAS1.T_ADS_FACT_SICB_ROLLWT where PROD_COILNO in (select distinct PROD_COILNO from BGTARAS1.PRE_L2_SUBPC20032 where ROLL_TYPE like 'W%' AND UNIT_CODE='Q402')
                            AND TYPE LIKE 'W%' AND UNIT_CODE='Q402' """
            conn_rds.execute(sql_del_3)

            rollString = ','.join(map(lambda x: "'" + x + "'", roll_df["roll_no"]))
            if len(rollString) > 0:
                sql_qty = """select * from TMSIJ4.TTMSIJ4RL WHERE roller_no in (""" + rollString + """)"""
                df_qty = util.query(conn_db2, sql=sql_qty)
                df2 = op.merge_df(roll_df, df_qty, [["roll_no"], ["roller_no"]], how="inner")
                df2 = df2[df2["grind_end_time"] <= df2["start_time"]]

                df2 = df2[
                    ['start_time', 'end_time', 'prod_coilno', 'roll_type', 'roll_no', 'roller_no', 'grind_start_time',
                     'grind_end_time', 'grind_qty']]
                df2.rename(columns={'prod_coilno': 'product_coil_no'}, inplace=True)

                PRE_L2_TTMSIJ4RL = """
                                CREATE TABLE BGTARAS1.PRE_L2_TTMSIJ4RL(
                                    START_TIME VARCHAR(14),
                                    END_TIME VARCHAR(14),
                                    PRODUCT_COIL_NO VARCHAR(11),
                                    ROLL_TYPE VARCHAR(5),
                                    ROLL_NO VARCHAR(10),
                                    ROLLER_NO VARCHAR(10),
                                    GRIND_START_TIME VARCHAR(14),
                                    GRIND_END_TIME VARCHAR(14),
                                    GRIND_QTY DECIMAL(24,6)
                                )NOT LOGGED INITIALLY
                                IN "BGTARAS1"
                            """
                # 建表
                util.createTmpTable(conn_rds, "BGTARAS1", "PRE_L2_TTMSIJ4RL", PRE_L2_TTMSIJ4RL)
                dtypedict2 = op.get_dict_type(df2)
                util.insert(df2, conn_rds, "PRE_L2_TTMSIJ4RL", "BGTARAS1", dtypedict2)

                # 把每个卷分摊的磨削量加入表T_ADS_FACT_SICB_ROLLWT中
                sql_qty = """
                                SELECT
                                    ROLLER_NO,
                                    GRIND_END_TIME AS ROLLING_END_TIME,
                                    END_TIME,
                                    ROLL_TYPE AS TYPE,
                                    GRIND_QTY AS ROLLING_QTY,
                                    PROD_COILNO,
                                    UNIT_CODE
                                FROM
                                    (SELECT
                                            A.ROLLER_NO,
                                            b.ROLL_TYPE,
                                            A.GRIND_END_TIME,
                                            B.START_TIME,
                                            A.GRIND_QTY,
                                            B.END_TIME,
                                            B.PROD_COILNO,
                                            B.UNIT_CODE,
                                            row_number() over(partition BY b.PROD_COILNO,a.ROLLER_NO ORDER BY ABS(to_date
                                            (A.GRIND_END_TIME, 'YYYYMMDDHH24MISS')-to_date(B.START_TIME,'YYYYMMDDHH24MISS')))
                                            AS NUM
                                        FROM
                                            BGTARAS1.PRE_L2_TTMSIJ4RL A,
                                            BGTARAS1.PRE_L2_SUBPC20032 b
                                        WHERE
                                            a.roller_no=b.roll_no
                                        AND A.GRIND_END_TIME<=B.START_TIME
                                        AND B.UNIT_CODE='Q402')
                                WHERE num=1
                            """
                df_result = util.query(conn_rds, sql=sql_qty)
                dtypedict = op.get_dict_type(df_result)
                util.insert(df_result, conn_rds, "T_ADS_FACT_SICB_ROLLWT", "BGTARAS1", dtypedict)

                # 删除影响的卷号信息 先删除后新增
                sql_mo_1 = """
                                delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                and a.type like 'X%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                and UNIT_CODE='Q402' and IND ='64400_X_COST'
                            """
                conn_rds.execute(sql_mo_1)

                sql_mo_2 = """
                                            delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                            select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                            and a.type like 'YZ%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                            and UNIT_CODE='Q402' and IND ='64400_YZ_COST'
                                        """
                conn_rds.execute(sql_mo_2)

                sql_mo_3 = """
                                           delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                                        select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                                        and a.type like 'W%' AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                                  and UNIT_CODE='Q402' and IND ='64300_COST'
                                         """
                conn_rds.execute(sql_mo_3)

                sql_mo_4 = """
                                           delete from BGTARAS1.T_ADS_FACT_SICB_MO0101_RCM where PROD_COILNO in (
                                                   select distinct a.PROD_COILNO from BGTARAS1.T_ADS_FACT_SICB_ROLLWT a inner join BGTARAS1.PRE_L2_SUBPC20032 b on a.roller_no=b.roll_no 
                                                   AND A.UNIT_CODE=B.UNIT_CODE WHERE A.UNIT_CODE='Q402')
                                           and UNIT_CODE='Q402' and IND ='FL'
                                        """
                conn_rds.execute(sql_mo_4)
    except Exception as e:
        print(e)

#入口函数
def preRCM(p_unit,start_time, end_time):
    global conn_rds,conn_db2
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    # conn_sts = getConnectionSTS('10.70.248.187', '21050 ', 'db10006', 'admin-bgtasoq000', 'admin')
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    if p_unit == "Q602":
        createQ602Roll(start_time, end_time)
        createSUBPC2009(start_time, end_time)
    elif p_unit =="Q402":
        createQ402Roll(start_time, end_time)
    conn_rds.close()
    # conn_sts.close()
    conn_db2.close()

if __name__ == '__main__':
    preRCM('Q602','20211118200000','20211119200000')